﻿using System;
using System.Collections.Concurrent;
using System.Configuration;
using System.Data.Common;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
using System.Collections.Generic;
using System.Collections;
using System.IO;
using SqlPerfomance.V2.DomainModel.Enums;
using SqlPerfomance.V2.DomainModel.NodeTree.Interfaces;
using SqlPerfomance.V2.DomainModel.Models;
using System.Data;

namespace SqlPerfomance.V2.DAL
{
	public abstract class AbstractRepository<T>
	{

		#region DbUpdate

		#region SqlUpdateQueries
		private const string UpdateSqlServerQuery = @"
DECLARE @NestedSet TABLE
(
	id int primary key,
	parent_id int null,
	HLevel INT,
	name varchar(50),
	Lft INT,
	Rgt INT,
	NodeNumber INT,
	NodeCount INT,
	MPath VARBINARY(4000)
);

WITH
cteBuildMP AS
(
	SELECT anchor.id, anchor.parent_id, HLevel = 1, anchor.name,
			MPath = CAST(CAST(anchor.id AS BINARY(4)) AS VARBINARY(4000))
	FROM {0} AS anchor
	WHERE parent_id IS NULL
	UNION ALL

	SELECT recur.id, recur.parent_id, HLevel = cte.HLevel + 1, recur.name,
			MPath = CAST(cte.MPath + CAST(recur.id AS BINARY(4)) AS VARBINARY(4000))
	FROM {0} AS recur
	INNER JOIN cteBuildMP AS cte
		ON cte.id = recur.parent_id 
)
 
INSERT INTO @NestedSet
SELECT id = mp.id, mp.parent_id, mp.HLevel, mp.name,
		Lft = CAST(0 AS INT),
		Rgt = CAST(0 AS INT),
		NodeNumber = ROW_NUMBER() OVER (ORDER BY mp.MPath),
		NodeCount = CAST(0 AS INT), mp.MPath
FROM cteBuildMP as mp

DECLARE @Left INT;

WITH
cteCountNodes AS 
(
	SELECT Id = SUBSTRING(h.MPath, (t.N*4) - 3, 4),
	NodeCount = COUNT(*)
	FROM @NestedSet h,
		dbo.Tally t
	WHERE t.N BETWEEN 1 AND DATALENGTH(MPath)/4
	GROUP BY SUBSTRING(h.MPath, (t.N * 4) - 3, 4)	
)

UPDATE @NestedSet
SET @Left = Lft = (hn.NodeNumber * 2) - hn.HLevel,
	Rgt = ((cn.NodeCount - 1) * 2) + @Left + 1
FROM @NestedSet hn
INNER JOIN cteCountNodes cn
	ON hn.id = cn.id
	
UPDATE
    {0}
SET
	{0}.[Level] = RAN.HLevel - 1,
    {0}.Lft = RAN.Lft,
	{0}.Rgt = RAN.Rgt
FROM
    {0} SI
INNER JOIN
    @NestedSet RAN
ON 
    SI.ID = RAN.ID
";

		private const string UpdateMySqlServerQuery = @"
SELECT rebuild_nested_set_tree();
";
		#endregion

		public void UpdateNestedSetTree(ConnectionStrings connectionStr)
		{
			string query = string.Empty;
			switch (connectionStr)
			{
				case ConnectionStrings.MySqlConnectionString:
					query = UpdateMySqlServerQuery; break;
				case ConnectionStrings.SqlServerConnectionString:
					query = string.Format(UpdateSqlServerQuery, GetNodeTreeTableName(typeof(NestedSetTreeNode))); break;
			}
			using (DbConnection conn = GetDbConnection(connectionStr))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = query;
					cmd.CommandTimeout = 360;
					cmd.ExecuteNonQuery();
				}
			}
		}

		#endregion

		#region DbSave

// ReSharper disable once FieldCanBeMadeReadOnly.Local
		private ConcurrentQueue<StringBuilder> _sqlServerInsertQueue = new ConcurrentQueue<StringBuilder>();

// ReSharper disable once FieldCanBeMadeReadOnly.Local

        private string GetIdentityInsertCommand(ConnectionStrings connectionString, string tableName)
        {
            if (connectionString == ConnectionStrings.SqlServerConnectionString) return string.Format("SET IDENTITY_INSERT {0} ON;", tableName);
            return string.Empty;
        }
		private ConcurrentQueue<StringBuilder> _mySqlServerInsertQueue = new ConcurrentQueue<StringBuilder>();
        private object _lock = new object();
        public void SaveRange(IEnumerable<INode<T>> nodes, ConnectionStrings connectionStr)
        {
            var table = GetNodeTreeTableName(typeof(T));
            StringBuilder sb = new StringBuilder();
            int maxInserts = 1000;
            int cnt = 1;
            sb.Append(GetIdentityInsertCommand(connectionStr, table));

            var tasks = new List<Task>();
            foreach (var item in nodes)
            {
                sb.Append(CreateInsertQueryStr(item, table, connectionStr));
                cnt++;
                if(cnt == maxInserts)
                {
                    sb.Append(GetIdentityInsertCommand(connectionStr, table));
                    string s = sb.ToString();
                    tasks.Add(Task.Factory.StartNew(() => RunSaveCommand(s, connectionStr)));
                    cnt = 1;
                    sb.Clear();
                    sb.Append(GetIdentityInsertCommand(connectionStr, table));
                }
            }
            Task.WaitAll(tasks.ToArray());
            sb.Append(GetIdentityInsertCommand(connectionStr, table));
            string query = sb.ToString();
            if(!string.IsNullOrEmpty(query)) RunSaveCommand(sb.ToString(), connectionStr);
        }

        private void RunSaveCommand(object state, ConnectionStrings connectionString)
        {
            string sb = state as string;
            if (string.IsNullOrEmpty(sb)) return;
            using (DbConnection conn = GetDbConnection(connectionString))
            {
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sb.ToString();
                    cmd.CommandTimeout = 360;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }catch(Exception ex)
                    {
                        File.AppendAllText(@"C:\dev\query.txt", sb);
                        throw;
                    }
                }
            }
        }

		public void NodeTreeSave(INode<T> node, ConnectionStrings connectionStr)
		{
			var table = GetNodeTreeTableName(typeof(T));
			switch (connectionStr)
			{
				case ConnectionStrings.SqlServerConnectionString:
					{
						var query = CreateInsertQuery(node, table, connectionStr);
                        query.Insert(0, string.Format("SET IDENTITY_INSERT {0} ON;", table));
                        query.AppendFormat("SET IDENTITY_INSERT {0} OFF;", table);



						_sqlServerInsertQueue.Enqueue(query);
						ParallelInsertToDb(_sqlServerInsertQueue, connectionStr);

					}
					break;
				case ConnectionStrings.MySqlConnectionString:
					{
						var mySqlquery = CreateInsertQuery(node, table, connectionStr);
						_mySqlServerInsertQueue.Enqueue(mySqlquery);

						ParallelInsertToDb(_mySqlServerInsertQueue, connectionStr);
					}
					break;
			}
		}

		private void ParallelInsertToDb(ConcurrentQueue<StringBuilder> insertQueue, ConnectionStrings connectionStr)
		{
			//foreach(var sqlQuery in insertQueue)
			//{
			//	using (DbConnection conn = GetDbConnection(connectionStr))
			//	{
			//		conn.Open();
			//		using (DbCommand cmd = conn.CreateCommand())
			//		{
			//			StringBuilder cmdText;
			//			insertQueue.TryDequeue(out cmdText);
			//			cmd.CommandText = cmdText.ToString();
			//			cmd.CommandTimeout = 360;
			//			cmd.ExecuteNonQuery();
			//		}
			//	}
			//}
			Parallel.ForEach(insertQueue, sqlQuery =>
			{
				using (DbConnection conn = GetDbConnection(connectionStr))
				{
					conn.Open();
					using (DbCommand cmd = conn.CreateCommand())
					{
						StringBuilder cmdText;
						insertQueue.TryDequeue(out cmdText);
						cmd.CommandText = cmdText.ToString();
						cmd.CommandTimeout = 360;
						cmd.ExecuteNonQuery();
					}
				}
			});
		}

        private string CreateInsertQueryStr(INode<T> node, string tableName, ConnectionStrings connStr)
        {
            return CreateInsertQuery(node, tableName, connStr).ToString();
        }

		private StringBuilder CreateInsertQuery(INode<T> node, string tableName, ConnectionStrings connStr)
		{
			PropertyInfo[] properties = typeof(T).GetProperties();

			var query = new StringBuilder();
			query.AppendFormat("INSERT INTO {0}(", tableName);

			foreach (var property in properties)
			{
				query.Append(property.Name + ", ");
			}

			query.Length -= 2;
			query.Append(") VALUES(");

			foreach (var property in properties)
			{
				var value = GetPropValue(node, property.Name);
				if (value is string)
				{
					value = "'" + value + "'";
				}
				query.Append((value ?? "NULL") + ", ");
			}

			query.Length -= 2;
			query.Append(");" + Environment.NewLine);
			return query;
		}
		#endregion

		#region DbRead
		#endregion

		protected DbConnection GetDbConnection(ConnectionStrings connectionStr)
		{
			ConnectionStringSettings configuration =
				ConfigurationManager.ConnectionStrings[connectionStr.ToString()];
			DbProviderFactory factory = DbProviderFactories.GetFactory(configuration.ProviderName);

			DbConnection conn = factory.CreateConnection();
			conn.ConnectionString = configuration.ConnectionString;
			return conn;
		}

		protected string GetNodeTreeTableName(Type nodeType)
		{
			return ConfigurationManager.AppSettings[nodeType.Name];
		}

		private static object GetPropValue(INode<T> src, string propName)
		{
			return src.Data.GetType().GetProperty(propName).GetValue(src.Data, null);
		}

		private int GetIdByElement(ConnectionStrings connectionString, string sql)
		{
			using (DbConnection conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = sql;
					DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					int cnt = 0;
					while (reader.Read()) cnt = reader["MaxId"] != DBNull.Value ? reader.GetInt32(0) : 0;
					return cnt;
				}
			}
		}

		public int GetMaxId(ConnectionStrings connectionString, string tableName)
		{
			string query = string.Format(@"SELECT MAX(id) AS MaxId 
							FROM {0};", tableName);
			return GetIdByElement(connectionString, query);
			
		}

		public int GetElementByPosition(ConnectionStrings connectionString, string tableName, int position)
		{
            string query = string.Empty;
            if(connectionString == ConnectionStrings.SqlServerConnectionString)
            {
                query = string.Format(@"SELECT MAX(q.id) AS MaxId FROM (SELECT TOP {0} * FROM {1} ORDER BY id)q", position, tableName);
            }
            else
            {
                query = string.Format(@"SELECT MAX(q.id) AS MaxId FROM (SELECT * FROM {0} ORDER BY id LIMIT {1})q" ,tableName, position);
            }
			
			return GetIdByElement(connectionString, query);
		}

		public void ExecuteQuery(string sql, ConnectionStrings connectionString)
		{
			using(var conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using(var cmd = conn.CreateCommand())
				{
					cmd.CommandText = sql;
					cmd.ExecuteNonQuery();
				}
			}
		}

        public void ClearTable(ConnectionStrings connectionString, string tableName)
        {
            string sql = string.Format("DELETE FROM {0}", tableName);
            ExecuteQuery(sql, connectionString);
        }

	}
}
